In this end the next video we will create a value weighted index and evaluated index actually best reflects
how the total market ought to say the average investor is invested.
However you will see that the creating the value weighted index is a bit more complicated and requires
also more data because for each constituent and for each single timestamp we need to calculate the market
capitalization and to calculate the market cap.
We need the stock price and the amount of stocks outstanding.
So let's start here and we have still imparted the stocks data frame with all of these stock prices
over time.
So are our six constituents and we also have already imparted the norm data frame with the normalized
the prices and also including here the price weighted index and the equal weighted index and now as
additional data requirement we import.
The listing is clean CSP that we actually derived in the exercises of part 1 and we save the data frame
and the variable listings.
So let's have a look so here we have all current listings on the New York Stock Exchange a nest egg
and Amex and that's also if I look at the last five rows.
So we have f in total over six thousand eight hundred listings.
And let's also call here the informal method and yet our listings data frame we have exactly what we
need.
So we have the last price.
So this is based on the end of Q1 2019 and we have the corresponding market capitalization.
So for example if we have a look here at American Airlines then we have here the last price of thirty
four dollars and the corresponding market capitalization which is year actually acquired high number.
So in the billions and having those numbers so then we can actually calculate the amount of shares outstanding
by dividing the market capitalization by the last price.
So first of all we set the December column as being the index of our listings data frame.
So this makes sense here.
So we have you on the left hand side now our new index and actually we are only interested in the ticker
symbols Amazon Boeing Disney IBM Coca-Cola and Microsoft.
So let's say if you're a list with our ticker in the Ariba ticker and then let slide see our listings
data frame and we only selected our six constituents and we are only interested in the columns.
Last price and market capitalization.
So we are overriding the listings data frame and let's have a look here.
So here we have our six constituents.
And the last price and the market cap and then actually we can calculate the the number of shares outstanding
by dividing a market cap by the last price.
And we can do this same with the DIF method.
So let's do this
so here we are getting the shares outstanding.
So still these are quite large numbers for those uh large companies and we can also create a new column
for our listings that are framed so the shares column by dividing the two columns.
So let's have a look.
So we have now on the right hand side the amount of shares for each company.
And that's again have a look at our stocks data frames.
So here we have uh the stock prices over time.
And actually our listings data frame and uh the amount of shares that we calculated here are based on
the end of Q1 2000 and nineteen.
So actually we would require for each and every time stamp here the amount of outstanding shares and
actually the amount of outstanding shares can change for example if the company issues new shares or
distributes a share dividend also in a stock split But uh for the time being so this is still a simplification.
We assume that the amount of outstanding shares for our six constituents they do not change uh from
two thousand and thirteen.
And uh two days.
So we are just taking for each and every time stamp here this amount of outstanding shares.
So as I said uh this is a simplification here.
And actually we would require even more data.
So for each and every time stamp the amount of outstanding shares but for the time being it's sufficient
for us to work with.
This the simplification here.
And actually what we have to do now we have to multiply for each and every time stamp here the stock
prices with the the amount of shares to calculate uh the uh market capitalization.
So for example he and the very first time stamp out the very first row.
You multiply here.
Three hundred ninety eight.
With uh this amount of shares of Amazon then we multiply one hundred thirty six point four nine for
Boeing with the amount of outstanding shares and so on and we have to do this for each single timestamp
and we can do this by taking uh the stock's data frame and apply the model method.
Then we pass the uh the panda series shares with the number of shares for each company and uh then we
also pass columns to the access parameter so actually our multiplication should be matched by the column
labels here of our stock state frame and then we are saving the resulting data frame in the web market
cap M cap.
So let's do this and let's have a look here.
So this is for each single timestamp the market capitalization for our six constituents.
So from the end of 2013 to two thousand and eighteen.
And it's also have a look at some meta information and we can also visualize that the market cap and
the evolution of the market cap for each company year by using the plot method.
So let's do this and let's have a look.
So here we can see in the dark blue the Amazon market capitalization.
So the value increased significantly from 2014 to today.
And for comparison reasons that let's all supply to the evolution of stock prices over time and we can
see it by far the highest stock prices has the Amazon stock.
So in terms of stock prices Amazon has by far the highest stock prices.
But in terms of market capitalization.
So Microsoft and Amazon are quite equal.
And the reason is simply that there are a lot of more shares outstanding for Microsoft than for Amazon.
So let's have a look here.
So these are the shares outstanding and for Microsoft we have a seven point six billion and two for
Everson.
Only four hundred ninety one million and in contrast to that the Amazon stock price is the much higher
than the Microsoft stock price.
And having here a combination by multiplying those two then we have quite similar uh market capitalizations
here all right.
This was the first video on creating a value weighted index.
So there was quite a lot of work to do here.
So to say kind of pre work and then the next video we will finally create our value weighted index.
So hope to see you there by.